/**
 * 
 */
package com.bbva.kyfb.kyfbutils.dao;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

import javax.persistence.Query;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.ArrayUtils;

import com.bbva.jee.arq.spring.core.gce.ExcepcionEjecucionAplicacion;
import com.bbva.kyfb.kyfbdto.firmas.errores.Errores_kyfb_servicios;

/**
 * The Class DaoUtils.
 *
 * @author xe20973
 */
public final class DaoUtils {


	/**
	 * Avoid instantiation.
	 */
	private DaoUtils() {

	}

	/**
	 * Adds the filter.
	 *
	 * @param column the column
	 * @param operand the operand
	 * @param param the param
	 * @param queryParams the query params
	 * @param and the and
	 * @param sdf the sdf
	 * @return the string
	 */
	public static String addFilter(final String column, final String operand, final Object param, final Map<Integer, Object> queryParams, final Boolean and, final SimpleDateFormat sdf) {
		try {
			final StringBuilder filterClause = new StringBuilder();
			if (param != null) {
				addAndOr(and, filterClause);
				filterClause.append(column).append(" ").append(operand).append(" ?").append(queryParams.size()+1).append(" ");
				setParamValue(param, queryParams, sdf);
			}
			return filterClause.toString();
		} catch (final Exception e) {
			throw new ExcepcionEjecucionAplicacion(Errores_kyfb_servicios.FORMATO_INCORRECTO, e);
		}
	}
	
	/**
	 * Adds the filter Nested.
	 *
	 * @param column the column
	 * @param operand the operand
	 * @param param the param
	 * @param queryParams the query params
	 * @param and the and
	 * @param sdf the sdf
	 * @return the string
	 */	
	public static String addFilterNested(final String[] columns, final String[] operands, final Object[] params, final Map<Integer, Object> queryParams, final Boolean andExterior, final Boolean[] andInteriores, final SimpleDateFormat sdf){
		try {
			final StringBuilder filterClause = new StringBuilder();
			
				addAndOr(andExterior, filterClause);
				
				filterClause.append("(");
				filterClause.append(columns[0]).append(" ").append(operands[0]).append(" ?").append(queryParams.size()+1).append(" ");
				setParamValue(params[0], queryParams, sdf);				
			
					for(int i=1;i<columns.length;i++){
						addFilter(columns[i],operands[i],params[i],queryParams,andInteriores[i],sdf);
					}
				
				filterClause.append(")");
	
			return filterClause.toString();
		} catch (Exception e) {
			throw new ExcepcionEjecucionAplicacion(Errores_kyfb_servicios.FORMATO_INCORRECTO, e);
		}
	}

	/**
	 * Adds the in clause.
	 *
	 * @param inColumn the in column
	 * @param params the params
	 * @param queryParams the query params
	 * @param and the and
	 * @param sdf the sdf
	 * @return the string
	 */
	public static String addInClause(final String inColumn, final Object[] params, final Map<Integer, Object> queryParams, final Boolean and, final SimpleDateFormat sdf) {
		try {
			final StringBuilder inClause = new StringBuilder();
			if (!ArrayUtils.isEmpty(params)) {
				addAndOr(and, inClause);

				inClause.append(inColumn).append(" IN (");

				for (final Object param : params) {
					inClause.append("?").append(queryParams.size()+1).append(", ");
					setParamValue(param, queryParams, sdf);
				}

				inClause.delete(inClause.length()-2, inClause.length()).append(") ");
			}
			return inClause.toString();
		} catch (final Exception e) {
			throw new ExcepcionEjecucionAplicacion(Errores_kyfb_servicios.FORMATO_INCORRECTO, e);
		}
	}

	/**
	 * Adds the order simple.
	 *
	 * @param params the params
	 * @return the string
	 */
	public static String addOrderSimple(final Object params[]) {
		try {
			final StringBuilder orderClause = new StringBuilder(" ORDER BY ");

			if(params[0]!=null){ //Ordenacion por defecto es la fecha de creacion
				orderClause.append(params[0]);
			}

			if(params[1]!=null){ //Ordenacion ascendente por defecto en DB2
				orderClause.append(" ").append(params[1]);
			}
			return orderClause.toString();
		} catch (final Exception e) {
			throw new ExcepcionEjecucionAplicacion(Errores_kyfb_servicios.FORMATO_INCORRECTO, e);
		}
	}

	/**
	 * Adds the paginate with order.
	 *
	 * @param currentQuery the current query
	 * @param params the params
	 * @param qryPrms the query params
	 * @return the stringBuilder
	 */
	public static StringBuilder addPaginate(final StringBuilder currentQuery,final Object params[], final Map<Integer, Object> qryPrms) {
		final StringBuilder paginateClause = new StringBuilder();
		if (params[0] != null && params[1]!=null) {
			paginateClause.append("SELECT * FROM (SELECT RDO.*, ROW_NUMBER() OVER() AS ROW_NEXT FROM(");
			paginateClause.append(currentQuery);

			final Object ordParams[]={params[2],params[3]};
			paginateClause.append(DaoUtils.addOrderSimple(ordParams));

			paginateClause.append(") AS RDO) WHERE ROW_NEXT BETWEEN ?").append(qryPrms.size()+1);
			setParamValue(params[0], qryPrms, null);
			paginateClause.append(" AND ?").append(qryPrms.size()+1);
			final Integer limite=(Integer)params[0]+(Integer)params[1]-1;
			setParamValue(limite, qryPrms, null);

		}

		return paginateClause;
	}

	/**
	 * Adds the Filter Fecha Actual.
	 *
	 * @param params the params
	 * @return the string
	 */
	public static String addFilterFuncionSQL(final Boolean and,final Object param, final String operand, final Object funcion) {
		try {
			StringBuilder filterFunctionClause = new StringBuilder();			
			
				addAndOr(and, filterFunctionClause);
			
				if(funcion!=null)
					filterFunctionClause.append(param+operand+funcion);
			
			return filterFunctionClause.toString();
		} catch (Exception e) {
			throw new ExcepcionEjecucionAplicacion(Errores_kyfb_servicios.FORMATO_INCORRECTO, e);
		}
	}	
	
	/**
	 * One record updated.
	 *
	 * @param updated the updated
	 */
	public static void oneRecordUpdated(final int updated) {
		if(updated==0) {
			throw new ExcepcionEjecucionAplicacion(Errores_kyfb_servicios.NO_EXISTE_LA_ORDEN_O_SU_ESTADO_NO_PERMITE_ELIMINARLA);
		} else if (updated > 1){
			throw new ExcepcionEjecucionAplicacion(Errores_kyfb_servicios.ERROR_BBDD);
		}
	}

	/**
	 * At least one record.
	 *
	 * @param resultado the resultado
	 */
	public static void atLeastOneRecord(final Collection<?> resultado) {
		if (CollectionUtils.isEmpty(resultado)) {
			throw new ExcepcionEjecucionAplicacion(Errores_kyfb_servicios.NO_EXISTEN_DATOS_A_LISTAR);
		}
	}

	/**
	 * At least one record.
	 *
	 * @param resultado the resultado
	 */
	public static void atLeastOneRecord(final Map<?, ?> resultado) {
		if (resultado==null || resultado.isEmpty()) {
			throw new ExcepcionEjecucionAplicacion(Errores_kyfb_servicios.NO_EXISTEN_DATOS_A_LISTAR);
		}
	}

	/**
	 * Adds the query params.
	 *
	 * @param paramValues the param values
	 * @param qry the q
	 */
	public static void addQueryParams(final Map<Integer, Object> paramValues, final Query qry) {
		for (final Map.Entry<Integer, Object> paramValue : paramValues.entrySet()) {
			qry.setParameter(paramValue.getKey(), paramValue.getValue());
		}
	}

	/**
	 * Adds the and or.
	 *
	 * @param and the and
	 * @param filterClause the filter clause
	 */
	private static void addAndOr(final Boolean and, final StringBuilder filterClause) {
		if (and==null) {
			filterClause.append(" WHERE ");
		} else if (and) {
			filterClause.append(" AND ");
		} else {
			filterClause.append(" OR ");
		}
	}

	/**
	 * Sets the param value.
	 *
	 * @param param the param
	 * @param queryParams the query params
	 * @param sdf the sdf
	 */
	private static void setParamValue(final Object param, final Map<Integer, Object> queryParams, final SimpleDateFormat sdf) {
		if (sdf == null) {
			queryParams.put(queryParams.size()+1, param);
		} else {
			queryParams.put(queryParams.size()+1, sdf.format(param));
		}
	}

	/**
	 * Recoge la lista de resultados como array de objetos y la devuelve como lista de array de String
	 * @param lstObj
	 * @return
	 */
	public static final List<String[]> transLstArrObjToLstArrStr(final List<Object[]> lstObj){
		final List<String[]> lstStr = new ArrayList<String[]>(lstObj.size());
		String[] arrStr;
		for(final Object[] arrObj:lstObj){
			arrStr = new String[arrObj.length];
			for(int i=0;i<arrObj.length;i++){
				arrStr[i]=(String)arrObj[i];
			}
			lstStr.add(arrStr);
		}
		return lstStr;
	}
}
